package gov.nhso.prov.acc.apps.acc67;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.zehon.FileTransferStatus;
import com.zehon.exception.FileTransferException;
import com.zehon.ftp.FTP;

import gov.nhso.prov.acc.beans.Acc67TransferDailyBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6FtpBean;
import gov.nhso.prov.acc.beans.Acc6ProvinceAccountBean;
import gov.nhso.prov.acc.beans.Acc6TempChequeStmtBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.beans.Acc7ProvinceAccountBean;
import gov.nhso.prov.acc.core.ConfigBundle;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.ftp.FTPUtil;
import gov.nhso.prov.acc.utils.DateUtil;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC670103 extends Utility {
	public String url = "/apps/acc67/ACC670103A.jsp";
	public ACC670103(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = request.getParameter("job");
		if (job.equals("init")) {
			this.init(request);
		} else if (job.equals("testConnectFTP")) {
			this.testConnectFTP(request);
		} else if (job.equals("importTranfer")) {
			this.importTranfer(request);
		} else if (job.equals("importCheque")) {
			this.importCheque(request);
		} else if (job.equals("downloadFile")) {
			this.downloadFile(request, response);
		} else if (job.equals("exportFile")) {
			this.exportFile(request, response);
		} else if (job.equals("endProcessTransfer")) {
			this.endProcessTransfer(request);
		} else if (job.equals("setErrMsg")) {
			this.setErrMsg(request);
		} else if (job.equals("forward2Report")) {
			this.forward2Report(request, response);
		} else if (job.equals("updateStatus")) {
			this.updateStatus(request);
		} else if (job.equals("importDailyStatement")) {
			this.importDailyStatement(request);
		} else if (job.equals("exportDailyStatement")) {
			this.exportDailyStatement(request, response);
		}
	}
	private String init(HttpServletRequest request) {
		this.initPage(request);
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("lsBudgetYearAc", this.dataUtil(dao).lsBudgetYearAc());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
		/*try {
			IEnterpriseSession sess = CrystalEnterprise.getSessionMgr().logon("Administrator", "passw0rd", "CTRSRV:6400", "secEnterprise");
			String token = sess.getLogonTokenMgr().createLogonToken("",120,100);
			ParameterFieldController rfc = null;
			request.setAttribute("token", token);
		} catch (Exception e) {
			e.printStackTrace();
		}*/
	}
	private String testConnectFTP(HttpServletRequest request) {
		String FTPHost = this.getAttr(request, "FTPHost");
		String FTPUserId = this.getAttr(request, "FTPUserId"); 
		String FTPPasswd = this.getAttr(request, "FTPPasswd"); 
		FTPClient client = new FTPClient();
		StringBuffer text = new StringBuffer();
		boolean login = false;
		try {
			
			client.connect(FTPHost);
			login = client.login(FTPUserId, FTPPasswd);
			if (login) {
				this.message(request).setMsg("I", "เชื่อมต่อสำเร็จ");
				text.append("Hello World.");
				InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
				FTPUtil ftp = new FTPUtil();
				String destFolder = "/"+ftp.ftpNhso2BaacTranfer+"/" + ftp.yyyy() + "/" + ftp.mm(); 
				String nameOfFile = "FileTest.txt";
				int status = FTP.sendFile(is, nameOfFile, destFolder, FTPHost, FTPUserId,  FTPPasswd);
				if (FileTransferStatus.SUCCESS == status) {
					this.message(request).setMsg("I", "ส่งไฟล์สำเร็จ");
				} else if(FileTransferStatus.FAILURE == status) {
					this.message(request).setMsg("E", "ส่งไฟล์ไม่สำเร็จ");
				}
				client.logout();
            } else {
    			this.message(request).setMsg("W", "ไม่สามารถติดต่อกับ FTP Host ได้เนื่องจาก user หรือ password ไม่ถูกต้อง ");
            }
			
		} catch (IOException e) {
			this.message(request).setMsg("E", "ไม่สามารถติดต่อกับ FTP Host ได้เนื่องจากไม่พบ FTP host นี้ : "+ FTPHost + " กรุณาตรวจสอบ");
		} catch (FileTransferException e) {
			this.message(request).setMsg("E", "ส่งไฟล์ไม่สำเร็จ");
		}
		return this.url;
	}
	
	private String importTranfer(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			int countFail = 0;
			int countFile = 0;
			Acc6FtpBean afb = new Acc6FtpBean();
			afb.setFtpType("T");
			afb.setFtpStatus("W");
			List lsafb;
			FTPUtil ftu = new FTPUtil();
			lsafb = dao.selectList(afb);
			FTPClient client = new FTPClient();
			String yyyy = this.getAttr(request, "yyyy");
			String mm = this.getAttr(request, "mm");
			client.connect(ftu.ftpHost);
			client.login(ftu.ftpUserId, ftu.ftpPasswd);
			String remoteFolder = ftu.ftpBaac2NhsoTranfer+"/" + yyyy + "/" + mm + "/"; 
			System.out.println("remoteFolder >> " + remoteFolder);
			FTPFile[] arrF = client.listFiles(remoteFolder);
			List<String> fftp = new ArrayList<String>();
			for (int n = 0; n < arrF.length; n++) {
				FTPFile ff = arrF[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fftp.add(ff.getName());
				}
			}
		    client.disconnect();
			for (int i = 0; i < lsafb.size(); i++) {
				afb = (Acc6FtpBean) lsafb.get(i);
				String nameOfFile = afb.getFileName() + ".OUT";
				InputStream in = null;
				try {
					if (fftp.contains(nameOfFile)) {
						in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftu.ftpHost, ftu.ftpUserId, ftu.ftpPasswd);
						InputStreamReader is = new InputStreamReader(in, "TIS-620");
						BufferedReader br = new BufferedReader(is);
						String read = br.readLine();
						String reasonCode = "";
						String tsts = "";
						String s = "";
						String f = "";
						String o = "";
						while (read != null) {
							reasonCode = read.substring(85, 87);
							tsts = read.substring(287, 304);
							s = read.substring(52, 59);
							f = read.substring(59, 66);
							o = read.substring(66, 73);
							if (!tsts.trim().equals("")) {
								Acc6TransactionBean atb = new Acc6TransactionBean();
								atb.setTransactionTimestamp(ftu.setTimeStamp2DB(tsts));
								atb.setReasonCode(reasonCode);
								atb.setReasonDesc(" Charge : Service = " + s.substring(0, 5) + "." + s.substring(5) + ", Fee = , " + f.substring(0, 5) + "." + f.substring(5) + ", Other = " + o.substring(0, 5) + "." + o.substring(5));
								dao.update(atb);
							}
						    read = br.readLine();
						}
						is.close();
						afb.setFtpStatus("Y");
						afb.setFtpDesc("PROCESS FTP SUCCESS");
						countFile++;
					} else {
						countFail++;
						afb.setFtpStatus("W");
						afb.setFtpDesc("inputStream null \""+nameOfFile+"\"");
					}
				} catch (Exception e) {
					countFail++;
					afb.setFtpStatus("W");
					afb.setFtpDesc("PROCESS FTP NOT SUCCESS");
				} finally {
					try {
						if (in != null) {
							in.close();
						}
						afb.setCreatedDate("");
						afb.setCompletedDate("(SYSDATE)");
						dao.update(afb);
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			
			String sql = "SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'T'";
			List lsDocNo = dao.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String docNo = dao.chkData(map.get("DOC_NO"));
				String num1 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
				String num2 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
				String num3 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
				if (!num2.equals("0")) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					if (num1.equals(num2)) {
						aeb.setExpenseStatus("Y");
					} else {
						aeb.setExpenseStatus("S");
					}
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				} else if (!num3.equals("0") && num1.equals(num3)) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					aeb.setExpenseStatus("N");
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				}
			}
			
			this.message(request).setMsg("I", "ไฟล์ที่พบทั้งหมด " + lsafb.size() + " ไฟล์ ทำสำเร็จ "+countFile+" ไฟล์ ทำไม่สำเร็จ " + countFail + " ไฟล์ ");
			request.setAttribute("yyyy", yyyy);
			request.setAttribute("mm", mm);
			request.setAttribute("lsBudgetYearAc", this.dataUtil(dao).lsBudgetYearAc());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String importCheque(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			FTPUtil ftu = new FTPUtil();
			int countfile = 0;
			int countFail = 0;
			String date = this.getAttr(request, "date");
			String[] strArr = null;
			if (date != null) {
				strArr = date.split("\\/");
			}
			String yyyy = String.valueOf(Integer.parseInt(strArr[2]) - 543);
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			Acc6TransactionBean acc6TransactionBean = new Acc6TransactionBean();
			List lsafb;
			boolean result = false;
			apab.setActive("Y");
			lsafb = dao.selectList(apab, " AND PROVINCE_ACCOUNT_DAILY IS NOT NULL ");
			Map mapData = new HashMap();
			FTPClient client = new FTPClient();
			client.connect(ftu.ftpHost);
			client.login(ftu.ftpUserId, ftu.ftpPasswd);
			String remoteFolder = ftu.ftpBaac2NhsoCheque+"/" + yyyy+strArr[1]+strArr[0] + "/";
			FTPFile[] f = client.listFiles(remoteFolder);
			List<String> fftp = new ArrayList<String>();
			for (int n = 0; n < f.length; n++) {
				FTPFile ff = f[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fftp.add(ff.getName());
				}
			}
		    client.disconnect();
			StringBuffer sb = new StringBuffer();
			DateUtil du = new DateUtil();
			for (int x = 0; x < lsafb.size(); x++) {
				apab = (Acc6ProvinceAccountBean) lsafb.get(x);
				String nameOfFile = "FZC009-"+apab.getProvinceAccountDaily()+"-"+yyyy+strArr[1]+strArr[0]+"-G001";
				InputStream in = null;
				try {
					if (fftp.contains(nameOfFile)) {
						in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftu.ftpHost,  ftu.ftpUserId,  ftu.ftpPasswd);
						InputStreamReader is = new InputStreamReader(in, "TIS-620");
						BufferedReader br = new BufferedReader(is);
						String read = br.readLine();
						String[] splitStr = null;
						while (read != null) {
							splitStr = read.split("\\|");
							// get data form Acc6Transaction
							if (splitStr[14].trim().equals("D") && (splitStr[12].trim() != null && !splitStr[12].trim().equals(""))) {
								result = false;
								acc6TransactionBean = new Acc6TransactionBean();
								acc6TransactionBean.setChequeNo(splitStr[12].trim());
								acc6TransactionBean.setExpenseAmount(splitStr[13].trim());
								Map<String, String> xcols = new HashMap<String, String>();
								xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
								acc6TransactionBean = (Acc6TransactionBean) dao.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
								
								Acc6TransactionBean atb = new Acc6TransactionBean();
								if (acc6TransactionBean.getTransactionTimestamp() != null) {
									atb.setChequeReceivingDate(du.changeDateToDB(ftu.changeFormatDate(splitStr[3].trim())));
									atb.setReasonCode("00");
									atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
									result = dao.update(atb , " AND PROVINCE_ID = '"+apab.getProvinceId()+"' AND CHEQUE_NO = '"+splitStr[12].trim()+"' AND REASON_CODE IS NULL ");
									dao.execDataSql("UPDATE " + this.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = "+du.changeDateToDB(ftu.changeFormatDate(splitStr[3].trim()))+" WHERE CHEQUE_NO = '" + splitStr[12].trim() + "' AND CHEQUE_AMOUNT = " + splitStr[13].trim() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
								}
								
								if (!result) {
									sb.delete(0, sb.length());
									sb.append("SELECT PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_TEMP_CHEQUE_STMT ");
									sb.append("WHERE PROVINCE_ID = '" + apab.getProvinceId() + "' AND CHEQUE_NO = '"+splitStr[12].trim()+"' ");
									sb.append("AND CHEQUE_AMOUNT = '"+splitStr[13].trim()+"' ");
									mapData = dao.findDataSql(sb.toString());
									if (mapData.size() <= 0) {//not find data
										sb.delete(0, sb.length());
										sb.append("INSERT INTO " + this.schema + ".ACC6_TEMP_CHEQUE_STMT (PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT,CHEQUE_RECEIVING_DATE,CREATED_DATE) ");
										sb.append("VALUES ('"+apab.getProvinceId()+"','"+splitStr[12].trim()+"',"+splitStr[13].trim()+","+du.changeDateToDB(ftu.changeFormatDate(splitStr[3].trim()))+",(SYSDATE)) ");
										dao.execDataSql(sb.toString());
									}
								}
							}
						    read = br.readLine();
						}
						is.close();
						countfile++;
					}
				} catch (Exception e) {
					countFail++;
					e.printStackTrace();
				} finally {
					try {
						if (in != null) {
							in.close();
						}
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			
			Acc6TempChequeStmtBean tcs = new Acc6TempChequeStmtBean();
			lsafb = dao.selectList(tcs);
			if (lsafb.size() > 0) {
				du = new DateUtil();
				for (int i=0;i<lsafb.size();i++) {
					Acc6TempChequeStmtBean bean = (Acc6TempChequeStmtBean)lsafb.get(i);
					result = false;
					acc6TransactionBean = new Acc6TransactionBean();
					acc6TransactionBean.setChequeNo(bean.getChequeNo());
					acc6TransactionBean.setExpenseAmount(bean.getChequeAmount());
					Map<String, String> xcols = new HashMap<String, String>();
					xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
					acc6TransactionBean = (Acc6TransactionBean) dao.select(acc6TransactionBean, xcols, " AND REASON_CODE IS NULL ");
					
					Acc6TransactionBean atb = new Acc6TransactionBean();
					if (acc6TransactionBean.getTransactionTimestamp() != null) {
						atb.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(bean.getCreatedDate(), "")+"','DD.MM.YYYY')");
						atb.setReasonCode("00");
						atb.setTransactionTimestamp("TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
						result = dao.update(atb , " AND PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO = '"+bean.getChequeNo()+"' AND REASON_CODE IS NULL ");
					}
					
					if (result) {
						Map data = dao.findDataSql("SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
						String docNo = dao.chkData(data.get("DOC_NO"));
						if (!docNo.equals("")) {
							dao.execDataSql("UPDATE " + this.schema + ".ACC6_EXPENSE_CHEQUE SET CHEQUE_RECEIVING_DATE = TO_DATE('"+du.changeDateFromDB(bean.getCreatedDate(), "")+"','DD.MM.YYYY') WHERE CHEQUE_NO = '" + bean.getChequeNo() + "' AND CHEQUE_AMOUNT = " + bean.getChequeAmount() + " AND TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + acc6TransactionBean.getTransactionTimestamp() + "', 'YYYYMMDDHH24MISSFF3')");
							dao.execDataSql("DELETE " + this.schema + ".ACC6_TEMP_CHEQUE_STMT WHERE PROVINCE_ID = '"+bean.getProvinceId()+"' AND CHEQUE_NO='"+bean.getChequeNo()+"' AND CHEQUE_AMOUNT = "+bean.getChequeAmount()+" AND TO_DATE(CREATED_DATE,'DD.MM.YYYY') = TO_DATE('"+du.changeDateFromDB(bean.getCreatedDate(), "")+"','DD.MM.YYYY') ");
						}
					}
				}
			}
			
			String sql = "SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'C'";
			List lsDocNo = dao.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String docNo = dao.chkData(map.get("DOC_NO"));
				String num1 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
				String num2 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
				String num3 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
				if (!num2.equals("0")) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					if (num1.equals(num2)) {
						aeb.setExpenseStatus("Y");
					} else {
						aeb.setExpenseStatus("S");
					}
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				} else if (!num3.equals("0") && num1.equals(num3)) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					aeb.setExpenseStatus("N");
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				}
			}
			
			this.message(request).setMsg("I", "ไฟล์ที่พบทั้งหมด " + fftp.size() + " ไฟล์ ทำสำเร็จ  "+ countfile +" ไฟล์ ทำไม่สำเร็จ " + countFail + " ไฟล์ ");
			request.setAttribute("date", date);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("W", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	@SuppressWarnings("unchecked")
	private String downloadFile(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		boolean isDownload = false; 
		String provinceId = this.getAttr(request, "provinceId");
		String trnDate = this.getAttr(request, "trnDate");
		String bankType = this.getAttr(request, "bankType");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer header = new StringBuffer();
			StringBuffer detail = new StringBuffer();
			StringBuffer total = new StringBuffer();
			StringBuffer text = new StringBuffer();
			String[] arrStr = null;
			String yyyy = "";
			String mm = "";
			String dd = "";
			int cf = 0;
			
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			apab.setActive("Y");
			apab.setProvinceId(provinceId);
			apab = (Acc6ProvinceAccountBean) dao.select(apab);
			if (trnDate != null && !trnDate.equals("")) {
				arrStr = trnDate.split("\\/");
				yyyy = String.valueOf(Integer.parseInt(arrStr[2])-543);
				mm = arrStr[1];
				dd = arrStr[0];
			}
			if (bankType != null && !bankType.equals("")) {
				if (bankType.equals("in")) {
					
					//HEADER RECORD sb.append
					header.delete(0, header.length());
					detail.delete(0, detail.length());
					total.delete(0, total.length());
					text.delete(0, text.length());
					header.append(this.fulFill("0", 1));
					header.append(this.fulFill("000001", 6));
					header.append(this.fulFill(apab.getProvinceVenderName(), 30));
					header.append(this.fulFill(apab.getProvinceAccountName(), 40));
					header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
					header.append(this.fulFill(yyyy + mm + dd, 8));
					header.append(this.fulFill(" ", 171));
					header.append(this.fulFill("100", 3));
					header.append(this.fulFill(" ", 125));
					header.append("\r\n");
					
					//DETAIL RECORDS
					String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
					" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
					" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
					" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
					" AND A.PROVINCE_ID = '" + provinceId + "' AND B.VENDER_BANK_CODE = '034' " +
					" AND A.TRANSACTION_TYPE IN ('T', 'A')";
					List lsatb = dao.listDataSql(sql);
					Map atb = null;
					double sum = 0d;
					String amount = "";
					String net = "";
					String branchCode = "";
					if (lsatb.size() > 0) {
						for (int j = 0; j < lsatb.size(); j++){
							atb = (Map) lsatb.get(j);
							amount = dao.chkData(atb.get("EXPENSE_AMOUNT"));
							net = amount;
							sum = sum + Double.parseDouble(amount);
							
							String accNo = dao.chkData(atb.get("VENDER_ACCOUNT")).trim();
							String bankCode = dao.chkData(atb.get("VENDER_BANK_CODE"));
							
							branchCode = "";
							if (bankCode.equals("034")) {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							} else {
								branchCode = dao.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
								if (branchCode != null && !branchCode.equals("")) {
									if (branchCode.trim().length() < 4) {
										for (int x=0;x<(4-branchCode.trim().length());x++) {
											branchCode = "0"+branchCode;
										}
									}
								}else {
									if (accNo.trim().length() == 10) {
										branchCode = "0" + accNo.substring(0, 3);
									} else if (accNo.trim().length() == 12) {
										branchCode = "0" + accNo.substring(2, 5);
									}
								}
							}

							detail.append(this.fulFill("1", 1));
							detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
							detail.append(this.fulFill(yyyy + mm + dd, 8));
							detail.append(this.fulFill(bankCode, 3));
							detail.append(this.fulFill(branchCode, 4));
							detail.append(this.fulFill(accNo, 16));
							detail.append(this.fulFill("C", 1));
							detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
							detail.append(this.fulFill("N", 1));
							detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
							detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
							detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
							detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
							detail.append(this.fulFill("??", 2));
							detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
							detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
							detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
							detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
							detail.append(this.fulFill(dao.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
							detail.append(this.fulFill(" ",32));
							detail.append("\r\n");		
						}
					}
					//TOTAL RECORD
					total.append(this.fulFill("2",1));
					total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
					total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
					total.append(this.fulFill(this.formatDecimal(sum,2), 15));
					total.append(this.fulFill(" ",242));
					total.append(this.fulFill(" ",128));
					
					if (lsatb.size() > 0) {
						cf++;
						branchCode = "";
						if (apab.getProvinceAccountSaving().trim().length() == 10) {
							branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
						} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
							branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
						}
						String[] arrStr2 = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
						if (arrStr2.length > 1) {
							for (int x=0;x<arrStr2.length;x++) {
								String[] arrBuffer = arrStr2[x].split("\\|");
								if (apab.getProvinceId().equals(arrBuffer[0])) {
									branchCode = arrBuffer[1];
									break;
								}
							}
						}
						text.append(header).append(detail).append(total);
						String nameOfFile = "HSOIN4" + yyyy + mm + dd +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;

						response.setContentType("text/html");
						response.setHeader("Content-Disposition", "attachment; filename=\""+ nameOfFile + "\"");
						response.setCharacterEncoding("windows-874");
						PrintWriter out = response.getWriter();
						out.print(text.toString());
						out.close();
						isDownload = true;
						
						Acc6FtpBean afb = new Acc6FtpBean();
						afb.setFileName(nameOfFile);
						List ls = dao.selectList(afb);
						if (ls.size() == 0) {
							try {
								afb.setFtpDesc("got download successfully.");
								afb.setFtpStatus("W");
							} catch (Exception e) {
								afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
								afb.setFtpStatus("F");
								e.printStackTrace();
							}
							dao.insert(afb);
						}
					}
					} else if (bankType.equals("out")) {
						FTPUtil fu = new FTPUtil();
						List holiDay = new ArrayList(Arrays.asList("20130101","20130225","20130401","20130408","20130415","20130416","20130501","20130506","20130524","20130701","20130722","20130812","20131023","20131205","20131210","20131231"));
						int bufferDD = Integer.parseInt(dd);
						int bufferMM = Integer.parseInt(mm)-1;
						int bufferYYYY = Integer.parseInt(yyyy);
						boolean result = fu.getWeekEnd(yyyy, mm, dd);
						String nextDay = "";
						String strHoliDay = fu.getNextDayFullFormat(bufferYYYY, bufferMM, bufferDD);
						String[] strArr = fu.getTomorrow(bufferYYYY, bufferMM, bufferDD+1).split("\\/");
						while (result || holiDay.contains(strHoliDay)) {//เป็นวันเสาร์ -วันอาทิตย์ หรือวันหยุด
							bufferDD++;
							nextDay = fu.getTomorrow(bufferYYYY, bufferMM, bufferDD);
							strArr = nextDay.split("\\/");
							result = fu.getWeekEnd(strArr[2], strArr[1], strArr[0]);
							strHoliDay = fu.getNextDayFullFormat(bufferYYYY, bufferMM, bufferDD);
						}
						
						header.append(this.fulFill("0", 1));
						header.append(this.fulFill("000001", 6));
						header.append(this.fulFill(apab.getProvinceVenderName(), 30));
						header.append(this.fulFill(apab.getProvinceAccountName(), 40));
						header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
						header.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
						header.append(this.fulFill(" ", 171));
						header.append(this.fulFill("100", 3));
						header.append(this.fulFill(" ", 125));
						header.append("\r\n");
						
						//DETAIL RECORDS
						String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
						" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
						" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
						" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
						" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE <> '034' " +
						" AND A.TRANSACTION_TYPE IN ('T', 'A')";
						List lsatb = dao.listDataSql(sql);
						Map atb = null;
						double sum = 0d;
						String amount = "";
						String net = "";
						String branchCode = "";
						if (lsatb.size() > 0) {
							for (int j = 0; j < lsatb.size(); j++){
								atb = (Map) lsatb.get(j);
								amount = dao.chkData(atb.get("EXPENSE_AMOUNT"));
								net = amount;
								sum = sum + Double.parseDouble(amount);
								String accNo = dao.chkData(atb.get("VENDER_ACCOUNT")).trim();
								String bankCode = dao.chkData(atb.get("VENDER_BANK_CODE"));
								
								branchCode = "";
								if (bankCode.equals("034")) {
									if (accNo.trim().length() == 10) {
										branchCode = "0" + accNo.substring(0, 3);
									} else if (accNo.trim().length() == 12) {
										branchCode = "0" + accNo.substring(2, 5);
									}
								} else {
									branchCode = dao.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
									if (branchCode != null && !branchCode.equals("")) {
										if (branchCode.trim().length() < 4) {
											for (int x=0;x<(4-branchCode.trim().length());x++) {
												branchCode = "0"+branchCode;
											}
										}
									}else {
										if (accNo.trim().length() == 10) {
											branchCode = "0" + accNo.substring(0, 3);
										} else if (accNo.trim().length() == 12) {
											branchCode = "0" + accNo.substring(2, 5);
										}
									}
								}
		
								detail.append(this.fulFill("1", 1));
								detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
								detail.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
								detail.append(this.fulFill(bankCode, 3));
								detail.append(this.fulFill(branchCode, 4));
								detail.append(this.fulFill(accNo, 16));
								detail.append(this.fulFill("C", 1));
								detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("N", 1));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("??", 2));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
								detail.append(this.fulFill(" ",32));
								detail.append("\r\n");
							}
						}
						//TOTAL RECORD
						total.append(this.fulFill("2",1));
						total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
						total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
						total.append(this.fulFill(this.formatDecimal(sum,2), 15));
						total.append(this.fulFill(" ",242));
						total.append(this.fulFill(" ",128));
						
						if (lsatb.size() > 0) {
							cf++;
							branchCode = "";
							if (apab.getProvinceAccountSaving().trim().length() == 10) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
							} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
							}
							String[] arrStr1 = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
							if (arrStr1.length > 1) {
								for (int x=0;x<arrStr1.length;x++) {
									String[] arrBuffer = arrStr1[x].split("\\|");
									if (apab.getProvinceId().equals(arrBuffer[0])) {
										branchCode = arrBuffer[1];
										break;
									}
								}
							}
							
							text.append(header).append(detail).append(total);
							String nameOfFile = "HSODC3" + strArr[2] + strArr[1] + strArr[0] +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
							
							response.setContentType("text/html");
							response.setHeader("Content-Disposition", "attachment; filename=\""+ nameOfFile + "\"");
							response.setCharacterEncoding("windows-874");
							
							PrintWriter out = response.getWriter();
							out.print(text.toString());
							out.close();
							isDownload = true;
							
							Acc6FtpBean afb = new Acc6FtpBean();
							afb.setFileName(nameOfFile);
							List ls = dao.selectList(afb);
							if (ls.size() == 0) {
								try {
									afb.setFtpDesc("got download successfully");
									afb.setFtpStatus("W");
								} catch (Exception e) {
									afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
									afb.setFtpStatus("F");
									e.printStackTrace();
								}
								dao.insert(afb);
							}
						}
					}
				}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (isDownload) {
					this.url = "";
				}else {
					this.message(request).setMsg("I", "ไม่สามารถ Download ไฟล์ได้");
					this.url = "/apps/acc67/ACC670103A.jsp";
					request.setAttribute("trnDate", trnDate);
					request.setAttribute("bankType", bankType);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	@SuppressWarnings("unchecked")
	private String exportFile(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer header = new StringBuffer();
			StringBuffer detail = new StringBuffer();
			StringBuffer total = new StringBuffer();
			StringBuffer text = new StringBuffer();
			String listDocNo = this.getAttr(request, "listDocNo");
			String provinceId = this.getAttr(request, "provinceIdEx");
			String trnDateExport = this.getAttr(request, "trnDateExport");
			String bankType = this.getAttr(request, "bankType");
			String[] arrStr = null;
			String yyyy = "";
			String mm = "";
			String dd = "";
			int cf = 0;
			String branchCode = "";
			String nameOfFile = "";
			
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			apab.setActive("Y");
			apab.setProvinceId(provinceId);
			apab = (Acc6ProvinceAccountBean) dao.select(apab);
			
			if (trnDateExport != null && !trnDateExport.equals("")) {
				arrStr = trnDateExport.split("\\/");
				yyyy = String.valueOf(Integer.parseInt(arrStr[2])-543);
				mm = arrStr[1];
				dd = arrStr[0];
			}
			if (bankType != null && !bankType.equals("")) {
				if (bankType.equals("in")) {
					if (apab.getProvinceVenderName() != null) {
						header.append(this.fulFill("0", 1));
						header.append(this.fulFill("000001", 6));
						header.append(this.fulFill(apab.getProvinceVenderName(), 30));
						header.append(this.fulFill(apab.getProvinceAccountName(), 40));
						header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
						header.append(this.fulFill(yyyy + mm + dd, 8));
						header.append(this.fulFill(" ", 171));
						header.append(this.fulFill("100", 3));
						header.append(this.fulFill(" ", 125));
						header.append("\r\n");
						
						//DETAIL RECORDS
						String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
						" , A.TRANSFER_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
						" FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
						//" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
						" WHERE B.VENDER_BANK_CODE = '034' AND A.DOC_NO IN ("+listDocNo+") ";
						List lsatb = dao.listDataSql(sql);
						Map atb = null;
						double sum = 0d;
						String amount = "";
						String net = "";
						if (lsatb.size() > 0) {
							for (int j = 0; j < lsatb.size(); j++){
								atb = (Map) lsatb.get(j);
								amount = dao.chkData(atb.get("TRANSFER_AMOUNT"));
								net = amount;
								sum = sum + Double.parseDouble(amount);
								
								String accNo = dao.chkData(atb.get("VENDER_ACCOUNT")).trim();
								String bankCode = dao.chkData(atb.get("VENDER_BANK_CODE"));
								
								branchCode = "";
								if (bankCode.equals("034")) {
									if (accNo.trim().length() == 10) {
										branchCode = "0" + accNo.substring(0, 3);
									} else if (accNo.trim().length() == 12) {
										branchCode = "0" + accNo.substring(2, 5);
									}
								} else {
									branchCode = dao.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
									if (branchCode != null && !branchCode.equals("")) {
										if (branchCode.trim().length() < 4) {
											for (int x=0;x<(4-branchCode.trim().length());x++) {
												branchCode = "0"+branchCode;
											}
										}
									}else {
										if (accNo.trim().length() == 10) {
											branchCode = "0" + accNo.substring(0, 3);
										} else if (accNo.trim().length() == 12) {
											branchCode = "0" + accNo.substring(2, 5);
										}
									}
								}

								detail.append(this.fulFill("1", 1));
								detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
								detail.append(this.fulFill(yyyy + mm + dd, 8));
								detail.append(this.fulFill(bankCode, 3));
								detail.append(this.fulFill(branchCode, 4));
								detail.append(this.fulFill(accNo, 16));
								detail.append(this.fulFill("C", 1));
								detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("N", 1));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("??", 2));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
								detail.append(this.fulFill(" ",32));
								detail.append("\r\n");		
							}
						}
						//TOTAL RECORD
						total.append(this.fulFill("2",1));
						total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
						total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
						total.append(this.fulFill(this.formatDecimal(sum,2), 15));
						total.append(this.fulFill(" ",242));
						total.append(this.fulFill(" ",128));
					
						if (lsatb.size() > 0) {
							cf++;
							branchCode = "";
							if (apab.getProvinceAccountSaving().trim().length() == 10) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
							} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
							}
							String[] arrStr2 = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
							if (arrStr2.length > 1) {
								for (int x=0;x<arrStr2.length;x++) {
									String[] arrBuffer = arrStr2[x].split("\\|");
									if (apab.getProvinceId().equals(arrBuffer[0])) {
										branchCode = arrBuffer[1];
										break;
									}
								}
							}
						}
						nameOfFile = "HSOIN4" + yyyy + mm + dd +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					}
				} else if (bankType.equals("out")) {
					if (apab.getProvinceVenderName() != null) {
						FTPUtil fu = new FTPUtil();
						List holiDay = new ArrayList(Arrays.asList("20130101","20130225","20130401","20130408","20130415","20130416","20130501","20130506","20130524","20130701","20130722","20130812","20131023","20131205","20131210","20131231"));
						int bufferDD = Integer.parseInt(dd);
						int bufferMM = Integer.parseInt(mm)-1;
						int bufferYYYY = Integer.parseInt(yyyy);
						boolean result = fu.getWeekEnd(yyyy, mm, dd);
						String nextDay = "";
						String strHoliDay = fu.getNextDayFullFormat(bufferYYYY, bufferMM, bufferDD);
						String[] strArr = fu.getTomorrow(bufferYYYY, bufferMM, bufferDD+1).split("\\/");
						while (result || holiDay.contains(strHoliDay)) {//เป็นวันเสาร์ -วันอาทิตย์ หรือวันหยุด
							bufferDD++;
							nextDay = fu.getTomorrow(bufferYYYY, bufferMM, bufferDD);
							strArr = nextDay.split("\\/");
							result = fu.getWeekEnd(strArr[2], strArr[1], strArr[0]);
							strHoliDay = fu.getNextDayFullFormat(bufferYYYY, bufferMM, bufferDD);
						}
					
						header.append(this.fulFill("0", 1));
						header.append(this.fulFill("000001", 6));
						header.append(this.fulFill(apab.getProvinceVenderName(), 30));
						header.append(this.fulFill(apab.getProvinceAccountName(), 40));
						header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
						header.append(this.fulFill(yyyy + mm + dd, 8));
						header.append(this.fulFill(" ", 171));
						header.append(this.fulFill("100", 3));
						header.append(this.fulFill(" ", 125));
						header.append("\r\n");
						
						//DETAIL RECORDS
						String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
						" , A.TRANSFER_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
						" FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
						//" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
						" WHERE B.VENDER_BANK_CODE <> '034' AND A.DOC_NO IN ("+listDocNo+") ";
						List lsatb = dao.listDataSql(sql);
						Map atb = null;
						double sum = 0d;
						String amount = "";
						String net = "";
						if (lsatb.size() > 0) {
							for (int j = 0; j < lsatb.size(); j++){
								atb = (Map) lsatb.get(j);
								amount = dao.chkData(atb.get("TRANSFER_AMOUNT"));
								net = amount;
								sum = sum + Double.parseDouble(amount);
								
								String accNo = dao.chkData(atb.get("VENDER_ACCOUNT")).trim();
								String bankCode = dao.chkData(atb.get("VENDER_BANK_CODE"));
								
								branchCode = "";
								if (bankCode.equals("034")) {
									if (accNo.trim().length() == 10) {
										branchCode = "0" + accNo.substring(0, 3);
									} else if (accNo.trim().length() == 12) {
										branchCode = "0" + accNo.substring(2, 5);
									}
								} else {
									branchCode = dao.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
									if (branchCode != null && !branchCode.equals("")) {
										if (branchCode.trim().length() < 4) {
											for (int x=0;x<(4-branchCode.trim().length());x++) {
												branchCode = "0"+branchCode;
											}
										}
									}else {
										if (accNo.trim().length() == 10) {
											branchCode = "0" + accNo.substring(0, 3);
										} else if (accNo.trim().length() == 12) {
											branchCode = "0" + accNo.substring(2, 5);
										}
									}
								}

								detail.append(this.fulFill("1", 1));
								detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
								detail.append(this.fulFill(yyyy + mm + dd, 8));
								detail.append(this.fulFill(bankCode, 3));
								detail.append(this.fulFill(branchCode, 4));
								detail.append(this.fulFill(accNo, 16));
								detail.append(this.fulFill("C", 1));
								detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("N", 1));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
								detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
								detail.append(this.fulFill("??", 2));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
								detail.append(this.fulFill(dao.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
								detail.append(this.fulFill(dao.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
								detail.append(this.fulFill(" ",32));
								detail.append("\r\n");		
							}
						}
						//TOTAL RECORD
						total.append(this.fulFill("2",1));
						total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
						total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
						total.append(this.fulFill(this.formatDecimal(sum,2), 15));
						total.append(this.fulFill(" ",242));
						total.append(this.fulFill(" ",128));
					
						if (lsatb.size() > 0) {
							cf++;
							branchCode = "";
							if (apab.getProvinceAccountSaving().trim().length() == 10) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
							} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
								branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
							}
							String[] arrStr2 = ConfigBundle.getValue("BRANCH_CODE").split("\\,");
							if (arrStr2.length > 1) {
								for (int x=0;x<arrStr2.length;x++) {
									String[] arrBuffer = arrStr2[x].split("\\|");
									if (apab.getProvinceId().equals(arrBuffer[0])) {
										branchCode = arrBuffer[1];
										break;
									}
								}
							}
						}	
						nameOfFile = "HSODC3" + strArr[2] + strArr[1] + strArr[0] +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					}
				}
			}
			
			text.append(header).append(detail).append(total);
			response.setContentType("text/html");
			response.setHeader("Content-Disposition", "attachment; filename=\""+nameOfFile+"\"");
			response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.print(text.toString());
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.url = "/apps/acc67/ACC670103A.jsp";
			this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				this.url = "/apps/acc67/ACC670103A.jsp";
				this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
			}
		}
		return this.url;
	}
	private String endProcessTransfer(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String docNo = this.getAttr(request, "docNo");
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			List listExpenseTransfer = new ArrayList();
			String ts = "";
			String expenseDate = "";
			String budgetYear = "";
			String provinceId = "";
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			expenseTransferBean.setDocNo(docNo);
			listExpenseTransfer = dao.selectList(expenseTransferBean, " ORDER BY TRANSFER_SEQ ASC ");
			if (listExpenseTransfer.size() > 0) {
				expenseBean.setDocNo(docNo);
				expenseBean = (Acc6ExpenseBean)dao.select(expenseBean);
				expenseDate = expenseBean.getExpenseDate();
				expenseDate = this.dateUtil().changeDateFromDB(expenseDate, "");
				budgetYear = expenseBean.getBudgetYear();
				provinceId = expenseBean.getProvinceId();
				for (int i = 0 ; i < listExpenseTransfer.size() ; i++) {
					Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
					ts = this.dateUtil().getCurrentTimeStamp2DB();
					transactionBean = new Acc6TransactionBean();
					expenseTransferBean = new Acc6ExpenseTransferBean();
					
					transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					transactionBean.setTransactionType("T");
					transactionBean.setBudgetYear(budgetYear);
					transactionBean.setProvinceId(provinceId);
					transactionBean.setExpenseDate("TO_DATE("+expenseDate+",'DD.MM.YYYY')");
					transactionBean.setRefNo(bean.getRefNo());
					transactionBean.setCategoryId(bean.getCategoryId());
					transactionBean.setVenderCode(bean.getVenderCode());
					transactionBean.setAllocateCode(bean.getAllocateCode());
					transactionBean.setExpenseAmount(bean.getTransferAmount());
					transactionBean.setIsBorrow("N");
					dao.insert(transactionBean);
					
					expenseTransferBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					expenseTransferBean.setDocNo(docNo);
					expenseTransferBean.setTransferSeq(bean.getTransferSeq());
					expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
					expenseTransferBean.setUpdatedDate(this.sysDate);
					dao.update(expenseTransferBean);
				}
				expenseBean = new Acc6ExpenseBean();
				expenseBean.setExpenseStatus("W");
				expenseBean.setDocNo(docNo);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setProvinceId(provinceId);
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedDate(this.sysDate);
				dao.update(expenseBean);
				this.message(request).setMsg("I", "บันทึกข้อมูลเสร็จสิ้น");
			} else {
				this.message(request).setMsg("W", "ไม่พบเลขที่เอกสาร : " + docNo);
			}
			
			request.setAttribute("docNo", docNo);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				this.url = "/apps/acc67/ACC670103A.jsp";
				this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
			}
		}
		return this.url;
	}
	private String setErrMsg(HttpServletRequest request) {
		request.getSession().setAttribute("SHOW_ERROR_MSG", "Y");
		return this.url;
	}
	private String forward2Report(HttpServletRequest request, HttpServletResponse response) {
		try {
			//IEnterpriseSession sess = CrystalEnterprise.getSessionMgr().logon("Administrator", "ProvaccSecure", "TPROVACCREPORT:6405", "secEnterprise");
			//String token = sess.getLogonTokenMgr().createLogonToken("",120,100);
			//String provinceId = this.getAttr(request, "provinceId");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			//
			/*this.url = "http://10.3.2.74.nhso.go.th:6405/BOE/OpenDocument/opendoc/openDocument.jsp?" 
					+ "lsSprovinceId=" + provinceId + "" 
					+ "&lsSbudgetYear=2556&lsSstartDate=20130101&lsSendDate=20130830"
					+ "&token="+token
					+"&sIDType=CUID&iDocID=AWVT2L6pbzBGmw3JhNWBEzQ";*/
			this.url = "http://192.168.203.112:8080/BOE/CrystalReports/viewrpt.cwr?&id=5632&sRefresh=Y" +
					   "&apsuser=Administrator&apspassword=ProvaccSecure" +
					   "&apsauthtype=secEnterprise&promptex-provinceId=1200&promptex-allocateCode=56010101" +
					   "&promptex-budgetYear=2556&promptex-startDate=20130101&promptex-endDate=20130731" ;
					  /* "&sbudgetYear=" +  budgetYear + "&sstartDate=" + startDate + "&sendDate=" + endDate + 
					   "&sallocateCode=" + allocateCode + "&sWindow=New"*/
			response.sendRedirect(this.url);
			//ถ้าต้องการให้เปิดเป็นหน้าใหม่ให้ไปใส่ Attributes target = "_blank" ที่ form
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.url = "/apps/acc67/ACC670103A.jsp";
		return this.url;
	}

	private String updateStatus(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String listDocNo = this.getAttr(request, "listDocNo2");
			List list = new ArrayList();
			String ts = "";
			String expenseDate = "";
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			if (listDocNo != null) {
				String[] strArr = listDocNo.split(",");
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					for (int i = 0 ; i < strArr.length ; i++ ) {
						list = new ArrayList();
						aeb = new Acc6ExpenseBean();
						aeb.setDocNo(strArr[i]);
						aeb.setExpenseStatus("A");
						dao.select(aeb);
						expenseDate = this.dateUtil().changeFormatDateFromDB(aeb.getExpenseDate(), "/");
						Acc6ExpenseTransferBean atb = new Acc6ExpenseTransferBean();
						atb.setDocNo(aeb.getDocNo());
						list = dao.selectList(atb, " ORDER BY TRANSFER_SEQ ASC");
						if (list.size() > 0) {
							for (int x = 0 ; x < list.size() ; x++) {
								ts = this.dateUtil().getCurrentTimeStamp2DB();
								transactionBean = new Acc6TransactionBean();
								atb = new Acc6ExpenseTransferBean();
								Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) list.get(x);
								
								transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
								transactionBean.setTransactionType("T");
								transactionBean.setBudgetYear(aeb.getBudgetYear());
								transactionBean.setProvinceId(aeb.getProvinceId());
								transactionBean.setExpenseDate(this.dateUtil().setTodateToDB(expenseDate));
								transactionBean.setRefNo(bean.getRefNo());
								transactionBean.setCategoryId(bean.getCategoryId());
								transactionBean.setVenderCode(bean.getVenderCode());
								transactionBean.setAllocateCode(bean.getAllocateCode());
								transactionBean.setExpenseAmount(bean.getTransferAmount());
								transactionBean.setIsBorrow(bean.getIsBorrow());
								transactionBean.setReasonCode("00");
								dao.insert(transactionBean);

								atb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
								atb.setDocNo(bean.getDocNo());
								atb.setTransferSeq(bean.getTransferSeq());
								atb.setUpdatedBy(this.user(request).getUserId());
								atb.setUpdatedDate(this.sysDate);
								dao.update(atb);
							}
						} 
						/*else {
							Acc6ExpenseChequeBean acb = new Acc6ExpenseChequeBean();
							acb.setDocNo(aeb.getDocNo());
							list = dao.selectList(acb, " ORDER BY CHEQUE_SEQ ASC");
							String chequeDate = "";
							if (list.size() > 0) {
								for (int x = 0 ; x < list.size() ; x++) {
									ts = this.dateUtil().getCurrentTimeStamp2DB();
									transactionBean = new Acc6TransactionBean();
									acb = new Acc6ExpenseChequeBean();
									Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean) list.get(x);
									chequeDate = this.dateUtil().changeFormatDateFromDB(bean.getChequeDate(), "/");
									
									transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
									transactionBean.setTransactionType("C");
									transactionBean.setBudgetYear(aeb.getBudgetYear());
									transactionBean.setProvinceId(aeb.getProvinceId());
									transactionBean.setExpenseDate(this.dateUtil().setTodateToDB(chequeDate));
									transactionBean.setRefNo(bean.getRefNo());
									transactionBean.setCategoryId(bean.getCategoryId());
									transactionBean.setVenderCode(bean.getVenderCode());
									transactionBean.setAllocateCode(bean.getAllocateCode());
									transactionBean.setExpenseAmount(bean.getChequeAmount());
									transactionBean.setChequeNo(bean.getChequeNo());
									transactionBean.setIsBorrow(bean.getIsBorrow());
									transactionBean.setTaxValue(bean.getTaxValue());
									transactionBean.setChequeDate(this.dateUtil().setTodateToDB(chequeDate));
									transactionBean.setReasonCode("00");
									dao.insert(transactionBean);

									acb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
									acb.setDocNo(bean.getDocNo());
									acb.setChequeSeq(bean.getChequeSeq());
									acb.setUpdatedBy(this.user(request).getUserId());
									acb.setUpdatedDate(this.sysDate);
									dao.update(acb);
								}
							}
						}*/
					if (list.size() > 0) {
						aeb = new Acc6ExpenseBean();
						aeb.setDocNo(strArr[i]);
						aeb.setExpenseStatus("Y");
						aeb.setUpdatedBy(this.user(request).getUserId());
						aeb.setUpdatedDate(this.sysDate);
						dao.update(aeb);
					}
				}
				/*else {
					Acc6ChequeBean acb = new Acc6ChequeBean();
					Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
					for (int i = 0 ; i < strArr.length ; i++ ) {
						acb = new Acc6ChequeBean();
						acb.setDocNo(strArr[i]);
						acb.setChequeStatus("A");
						dao.select(acb);
						list = dao.selectList(acdb, " ORDER BY CHEQUE_SEQ ASC ");
						String chequeDate = this.dateUtil().changeFormatDateFromDB(acb.getChequeDate(), "/");
						if (list.size() > 0) {
							for (int x = 0 ; x < list.size() ; x++) {
								Acc6ChequeDetailBean bean = (Acc6ChequeDetailBean) list.get(x);
								ts = this.dateUtil().getCurrentTimeStamp2DB();
								transactionBean = new Acc6TransactionBean();
								
								transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
								transactionBean.setTransactionType("C");
								transactionBean.setBudgetYear(bean.getBudgetYear());
								transactionBean.setProvinceId(acb.getProvinceId());
								transactionBean.setExpenseDate(this.dateUtil().setTodateToDB(chequeDate));
								transactionBean.setChequeDate(this.dateUtil().setTodateToDB(chequeDate));
								transactionBean.setRefNo(bean.getRefNo());
								transactionBean.setCategoryId(bean.getCategoryId());
								transactionBean.setVenderCode(bean.getVenderCode());
								transactionBean.setAllocateCode(bean.getAllocateCode());
								transactionBean.setExpenseAmount(bean.getChequeAmount());
								transactionBean.setTaxValue(bean.getTaxValue());
								transactionBean.setIsBorrow(bean.getIsBorrow());
								transactionBean.setChequeNo(acb.getChequeNo());
								dao.insert(transactionBean);
								
								acdb.setDocNo(acb.getDocNo());
								acdb.setChequeSeq(bean.getChequeSeq());
								acdb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
								acdb.setUpdatedBy(this.user(request).getUserId());
								acdb.setUpdatedDate(this.sysDate);
								dao.update(acdb);
							}
							acb = new Acc6ChequeBean();
							acb.setDocNo(strArr[i]);
							acb.setChequeStatus("Y");
							acb.setUpdatedBy(this.user(request).getUserId());
							acb.setUpdatedDate(this.sysDate);
							dao.update(acb);
						}
					}
					
				}*/
				this.message(request).setMsg("I", "บันทึกข้อมูลเสร็จสิ้น");
			} else {
				this.message(request).setMsg("W", "รูปแบบข้อมูลของเลขที่เอกสารไม่ถูกต้อง กรุณาระบุใหม่");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				this.url = "/apps/acc67/ACC670103A.jsp";
				this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
			}
		}
		return this.url;
	}
	
	private String importDailyStatement(HttpServletRequest request) {
		
		System.out.println(" >>>>>>  importDailyStatement ");
		
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			Acc7ProvinceAccountBean apab7 = new Acc7ProvinceAccountBean();
			Acc67TransferDailyBean atdb = new Acc67TransferDailyBean();
			StringBuffer sb = new StringBuffer();
			String dailyStatementStartDate = this.getAttr(request, "dailyStatementStartDate");
			String dailyStatementEndDate = this.getAttr(request, "dailyStatementEndDate");
			int iStartDate = 0;
			int iEndDate = 0;
			Map map = new HashMap();
			
			FTPClient client = new FTPClient();
			FTPUtil ftu = new FTPUtil();
			client.connect(ftu.ftpHost);
			client.login(ftu.ftpUserId, ftu.ftpPasswd);
			//String remoteFolder = this.ftpBaac2NhsoCheque + "/20131031/"; //for test
			iStartDate = Integer.parseInt(this.dateUtil().changeFormatDate2YYYYMMDD(dailyStatementStartDate, "/", ""));
			iEndDate = Integer.parseInt(this.dateUtil().changeFormatDate2YYYYMMDD(dailyStatementEndDate, "/", ""));
			
			System.out.println(" start date : " + iStartDate);
			System.out.println(" end data : " + iEndDate );
			
			String remoteFolder = ftu.ftpBaac2NhsoCheque+"/";
			FTPFile[] f = client.listFiles(remoteFolder);
			int fileName = 0;
			List<String> lsFolderName = new ArrayList<String>();
			for (int n = 0; n < f.length; n++) {
				FTPFile ff = f[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fileName = Integer.parseInt(ff.getName());
					if (fileName >= iStartDate && fileName <= iEndDate) {
					
						System.out.println(" get file name  : " +  ff.getName());
					
						lsFolderName.add(ff.getName());
					}
				}
			}
			
		    String accountNumber = "";//2
		    String accountType = "";//แบ่งเป็นสองประเภท 1.บัญชีออมทรัพย์ 2.กระแสรายวัน
		    String transferDate = "";//10
		    String transactionCode = "";//11
		    String chequeNo = ""; //12
		    String amount = "";//13
		    String transferType = "";//14
		    String errorField = "";//15
		    String remain = "";//16
		    String date = "";
		    String[] strArr = null;
		    String[] arrStr = null;
		    String dd = "";
		    String mm = "";
		    String yyyy = "";
		    String transferDailyKey = "";
			String[] splitStr = null;
			String provinceId = "";
			String provinceName = "";

			for (int i = 0 ; i < lsFolderName.size() ; i++) {
				remoteFolder = ftu.ftpBaac2NhsoCheque+"/"+lsFolderName.get(i)+"/";
				f = client.listFiles(remoteFolder);
				for (int n = 0; n < f.length; n++) {
					FTPFile ff = f[n];
					if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
							//Account 6
						provinceId = "";
						provinceName = "";
						accountType = "";
						String nameOfFile = ff.getName();
						arrStr = nameOfFile.split("-");
						sb.setLength(0);
						sb.append("SELECT PROVINCE_ID,PROVINCE_NAME FROM " + this.schema + ".ACC6_PROVINCE_ACCOUNT ");
						accountNumber = dao.chkData(arrStr[1]);
						sb.append("WHERE ");
						if (accountNumber.substring(0, 2).equals("01")) {
							sb.append("PROVINCE_ACCOUNT_SAVING = '" +accountNumber+ "' ");
							accountType = "S";
						} else {
							sb.append("PROVINCE_ACCOUNT_DAILY = '" +accountNumber+ "' ");
							accountType = "D";
						}
						map = dao.findDataSql(sb.toString());
						//apab6.setProvinceAccountSaving(arrStr[1]);
						//dao.select(apab6);
						InputStream in = null;
						try {
							if (!dao.chkData(map.get("PROVINCE_ID")).equals("")) {
								provinceId = dao.chkData(map.get("PROVINCE_ID"));
								provinceName = dao.chkData(map.get("PROVINCE_NAME"));
								in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftu.ftpHost,  ftu.ftpUserId,  ftu.ftpPasswd);
								InputStreamReader is = new InputStreamReader(in, "TIS-620");
								BufferedReader br = new BufferedReader(is);
								String read = br.readLine();
								while (read != null) {
									splitStr = read.split("\\|");
									transferType = dao.chkData(splitStr[14]);
									errorField = dao.chkData(splitStr[15]);
									//if (transferType.equals("D") && !errorField.equals("E")) {
									atdb = new Acc67TransferDailyBean();
									date = ftu.changeFormatDate2AD(dao.chkData(splitStr[10]));
									strArr = date.split("\\/");
									mm = strArr[1];
									yyyy = strArr[2];
									String lastOfMonth = ftu.getLastOfMonth(yyyy, mm);
									
									date = ftu.changeFormatDate2AD(dao.chkData(splitStr[10]));
									strArr = date.split("\\/");
									dd = strArr[0];
									mm = strArr[1];
									yyyy = strArr[2];
									date = yyyy+mm+dd;
									
									accountNumber = dao.chkData(splitStr[2]);
									transferDate = ftu.changeFormatDate2YYYYMMDDD(dao.chkData(splitStr[10]));
									transactionCode = dao.chkData(splitStr[11]);
									chequeNo = dao.chkData(splitStr[12]); // new boy
									amount = dao.chkData(splitStr[13]);
									remain = dao.chkData(splitStr[16]);
									if (accountType.equals("D")) {
										remain = "0";
									}
									atdb.setProvinceId(provinceId);
									atdb.setProvinceName(provinceName);
									atdb.setTransferDailyAcc("6");
									atdb.setAccountNumber(accountNumber);
									atdb.setAccountType(accountType);
									atdb.setTransferDate(transferDate);
									atdb.setTransactionCode(transactionCode.trim());
									atdb.setAmount(amount);
									atdb.setChequeNo(chequeNo); // new boy
									atdb.setTransferType(transferType);
									atdb.setRemain(remain);
									read = br.readLine();
									if (read == null && date.equals(lastOfMonth)) {
										atdb.setLastFieldOfMonth("Y");
									} else {
										atdb.setLastFieldOfMonth("N");
									}
									transferDailyKey = provinceId + dao.chkData(splitStr[3]) + dao.chkData(splitStr[4]);
									atdb.setTransferDailyKey(transferDailyKey);
									sb.setLength(0);
									sb.append("SELECT NVL(COUNT(TRANSFER_DAILY_KEY),0) AS COUNT_DATA FROM " + this.schema + ".ACC67_TRANSFER_DAILY WHERE TRANSFER_DAILY_KEY = '" + transferDailyKey +"'");
									map = dao.findDataSql(sb.toString());
									if (Integer.parseInt(dao.chkData(map.get("COUNT_DATA"))) == 0 ) {
										if (accountType.equals("S")) {
											dao.insert(atdb);
										} else {
											if (transferType.equals("D") && !errorField.equals("E")) {
												System.out.println("---- test insert data ----" );
												dao.insert(atdb);
											}
										}
									}
									//}
								}
								is.close();
							}
						} catch (Exception e) {
							System.out.println("printStackTrace Class BACC2NHSO Method ftpBaac2NhsoDailyStatement Acc6");
							e.printStackTrace();
							dao.printMessage();
						} finally {
							try {
								if (in != null) {
									in.close();
								}
							} catch (IOException e) {
								e.printStackTrace();
							}
						}
						//Account 7
						apab7 = new Acc7ProvinceAccountBean();
						apab7.setProvinceAccount(arrStr[1]);
						dao.select(apab7);
						in = null;
						try {
							if (apab7.getProvinceId() != null && !apab7.getProvinceId().equals("")) {
								in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftu.ftpHost,  ftu.ftpUserId,  ftu.ftpPasswd);
								InputStreamReader is = new InputStreamReader(in, "TIS-620");
								BufferedReader br = new BufferedReader(is);
								String read = br.readLine();
								while (read != null) {
									splitStr = read.split("\\|");
									atdb = new Acc67TransferDailyBean();
									date = ftu.changeFormatDate2AD(dao.chkData(splitStr[10]));
									strArr = date.split("\\/");
									mm = strArr[1];
									yyyy = strArr[2];
									String lastOfMonth = ftu.getLastOfMonth(yyyy, mm);
									
									date = ftu.changeFormatDate2AD(dao.chkData(splitStr[10]));
									strArr = date.split("\\/");
									dd = strArr[0];
									mm = strArr[1];
									yyyy = strArr[2];
									date = yyyy+mm+dd;
									
									accountNumber = dao.chkData(splitStr[2]);
									transferDate = ftu.changeFormatDate2YYYYMMDDD(dao.chkData(splitStr[10]));
									transactionCode = dao.chkData(splitStr[11]);
									amount = dao.chkData(splitStr[13]);
									transferType = dao.chkData(splitStr[14]);
									remain = dao.chkData(splitStr[16]);
									atdb.setProvinceId(apab7.getProvinceId());
									atdb.setProvinceName(apab7.getProvinceName());
									atdb.setTransferDailyAcc("7");
									atdb.setAccountNumber(accountNumber);
									atdb.setAccountType("S");
									atdb.setTransferDate(transferDate);
									atdb.setTransactionCode(transactionCode.trim());
									atdb.setAmount(amount);
									atdb.setTransferType(transferType);
									atdb.setRemain(remain);
									read = br.readLine();
									if (read == null && date.equals(lastOfMonth)) {
										atdb.setLastFieldOfMonth("Y");
									} else {
										atdb.setLastFieldOfMonth("N");
									}
									transferDailyKey = apab7.getProvinceId() + dao.chkData(splitStr[3]) + dao.chkData(splitStr[4]);
									atdb.setTransferDailyKey(transferDailyKey);
									sb.setLength(0);
									sb.append("SELECT NVL(COUNT(TRANSFER_DAILY_KEY),0) AS COUNT_DATA FROM " + this.schema + ".ACC67_TRANSFER_DAILY WHERE TRANSFER_DAILY_KEY = '" + transferDailyKey +"'");
									map = dao.findDataSql(sb.toString());
									if (Integer.parseInt(dao.chkData(map.get("COUNT_DATA"))) == 0 ) {
										dao.insert(atdb);
									}
								}
								is.close();
							}
						} catch (Exception e) {
							System.out.println("printStackTrace Class BACC2NHSO Method ftpBaac2NhsoDailyStatement Acc7");
							e.printStackTrace();
							dao.printMessage();
						}
					}
				}
			}
			request.setAttribute("dailyStatementStartDate", this.getAttr(request, "dailyStatementStartDate"));
			request.setAttribute("dailyStatementEndDate", this.getAttr(request, "dailyStatementEndDate"));
		  
			// this
			this.cleanTransactionAccountType(conn , dao);
			
			 client.disconnect();
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				this.url = "/apps/acc67/ACC670103A.jsp";
				this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
			}
		}
		return this.url;
	}

	private String exportDailyStatement(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer text = new StringBuffer();
			StringBuffer sb = new StringBuffer();
			FTPClient client = new FTPClient();
			String accountType = "";
			String exportDailyStatementStartDate = this.getAttr(request, "exportDailyStatementStartDate");
			String exportDailyStatementEndDate = this.getAttr(request, "exportDailyStatementEndDate");
			int iStartDate = 0;
			int iEndDate = 0;
			String[] arrStr = null;
			Map map = new HashMap();
			FTPUtil ftu = new FTPUtil();
			client.connect(ftu.ftpHost);
			client.login(ftu.ftpUserId, ftu.ftpPasswd);
			iStartDate = Integer.parseInt(this.dateUtil().changeFormatDate2YYYYMMDD(exportDailyStatementStartDate, "/", ""));
			iEndDate = Integer.parseInt(this.dateUtil().changeFormatDate2YYYYMMDD(exportDailyStatementEndDate, "/", ""));
			String remoteFolder = ftu.ftpBaac2NhsoCheque+"/";
			FTPFile[] f = client.listFiles(remoteFolder);
			int fileName = 0;
			List<String> lsFolderName = new ArrayList<String>();
			for (int n = 0; n < f.length; n++) {
				FTPFile ff = f[n];
				if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
					fileName = Integer.parseInt(ff.getName());
					if (fileName >= iStartDate && fileName <= iEndDate) {
						lsFolderName.add(ff.getName());
					}
				}
			}
			String accountNumber = "";//2
		    String transferDate = "";//10
		    String transactionCode = "";//11
		    String amount = "";//13
		    String transferType = "";//14
		    String remain = "";//16
		    String provinceId = "";
		    String provinceName = "";
			String transferDailyKey = "";
		    String[] splitStr = null;
			for (int i = 0 ; i < lsFolderName.size() ; i++) {
				remoteFolder = ftu.ftpBaac2NhsoCheque+"/"+lsFolderName.get(i)+"/";
				f = client.listFiles(remoteFolder);
				for (int n = 0; n < f.length; n++) {
					FTPFile ff = f[n];
					if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
						String nameOfFile = ff.getName();
						arrStr = nameOfFile.split("-");
						sb.setLength(0);
						sb.append("SELECT PROVINCE_ID,PROVINCE_NAME FROM " + this.schema + ".ACC6_PROVINCE_ACCOUNT ");
						accountNumber = dao.chkData(arrStr[1]);
						sb.append("WHERE ");
						if (accountNumber.substring(0, 2).equals("01")) {
							sb.append("PROVINCE_ACCOUNT_SAVING = '" +accountNumber+ "' ");
							accountType = "S";
						} else {
							sb.append("PROVINCE_ACCOUNT_DAILY = '" +accountNumber+ "' ");
							accountType = "D";
						}
						map = dao.findDataSql(sb.toString());
						InputStream in = null;
						try {
							if (!dao.chkData(map.get("PROVINCE_ID")).equals("")) {
								in = FTP.getFileAsStream(nameOfFile, remoteFolder, ftu.ftpHost,  ftu.ftpUserId,  ftu.ftpPasswd);
								InputStreamReader is = new InputStreamReader(in, "TIS-620");
								BufferedReader br = new BufferedReader(is);
								String read = br.readLine();
								while (read != null) {
									splitStr = read.split("\\|");
									accountNumber = dao.chkData(splitStr[2]);
									transferDate = ftu.changeFormatDate2YYYYMMDDD(dao.chkData(splitStr[10]));
									transactionCode = dao.chkData(splitStr[11]);
									amount = dao.chkData(splitStr[13]);
									transferType = dao.chkData(splitStr[14]);
									remain = dao.chkData(splitStr[16]);
									provinceId = dao.chkData(map.get("PROVINCE_ID"));
									provinceName = dao.chkData(map.get("PROVINCE_NAME"));
									if (accountType.equals("S")) {
										remain = "0";
									}
									transferDailyKey = provinceId + dao.chkData(splitStr[3]) + dao.chkData(splitStr[4]);
									text.append("INSERT INTO "+this.schema+".ACC67_TRANSFER_DAILY ( PROVINCE_ID, PROVINCE_NAME,TRANSFER_DAILY_ACC, ACCOUNT_NUMBER ");
									text.append(", ACCOUNT_TYPE, TRANSFER_DATE, TRANSACTION_CODE, TRANSFER_TYPE, AMOUNT, REMAIN, LAST_FIELD_OF_MONTH, TRANSFER_DAILY_KEY) ");
									text.append("VALUES ( '"+provinceId+"', '"+provinceName+"', '6', '"+accountNumber+"', '"+accountType+"', '"+transferDate+"',");
									text.append(" '"+transactionCode+"', '"+transferType+"', "+amount+", "+remain+", 'N', '"+transferDailyKey+"')");
									text.append("\r\n");		
								}
								is.close();
							}
						} catch (Exception e) {
							System.out.println("printStackTrace Class BACC2NHSO Method ftpBaac2NhsoDailyStatement Acc6");
							e.printStackTrace();
							dao.printMessage();
						} finally {
							try {
								if (in != null) {
									in.close();
								}
							} catch (IOException e) {
								e.printStackTrace();
							}
						}
					}
				}
			}
			
			response.setContentType("text/html");
			response.setHeader("Content-Disposition", "attachment; filename=\"DataExportAcc6\"");
			response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.print(text.toString());
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.url = "/apps/acc67/ACC670103A.jsp";
			this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				this.url = "/apps/acc67/ACC670103A.jsp";
				this.message(request).setMsg("W", "เกิดข้อผิดพลาด");
			}
		}
		return this.url;
	}
	
	/**
	 * @description delete current account.
	 * @param conn
	 * @param isud
	 * @return
	 */
	public  boolean cleanTransactionAccountType( Connection conn, ISUD isud ){ 
		
		boolean action  = false;
	
		try {
			
			String sql  = "DELETE FROM " +this.schema + ".ACC67_TRANSFER_DAILY WHERE TRANSFER_DAILY_ACC='6' and ACCOUNT_TYPE='D' and  transfer_type='C' ";
			
			if(isud.execDataSql(sql)) {
					  action = true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return action;

	}
}
